﻿using API_Service.DBHelper;
using Dapper;
using System;
using System.Data;
using System.Linq;
using System.Reflection;

namespace API_Service.BizLogic.BasicData
{
    public class CustomService : BaseLogicService
    {
        private string _erpConnString = "";
        public CustomService()
        {
            _erpConnString = SqlCreator.ConnectionStrings["yfdb"];
        }

        public ExecutionResult<dynamic> SyncCustomDataFromERP()
        {
            ExecutionResult<dynamic> result = new ExecutionResult<dynamic>();
            try
            {
                string _strMaxCreateTime = "", _strMaxUpdateTime = "";
                //获取MES系统中当前物料最后更新时间。
                result = this.GetMESLastCustomERPDate();
                if (result.Status)
                {
                    var _tmpDate = result.Datas.AsList()[0];
                    _strMaxCreateTime = String.IsNullOrEmpty(_tmpDate.erp_create_time) ? "19990101" : _tmpDate.erp_create_time;
                    _strMaxUpdateTime = String.IsNullOrEmpty(_tmpDate.erp_update_time) ? "19990101" : _tmpDate.erp_update_time;
                    
                    //获取ERP更新数据
                    result = this.QueryERPNewCustomInfo(_strMaxCreateTime, _strMaxUpdateTime);
                    if (result.Status)
                    {
                        //循环取数，并更新MES库数据
                        foreach (var _tmpMat in result.Datas.ToList())
                        {
                            bool _bExists = false;
                            //CustomInfo _tmpMat = (CustomInfo)mat;
                            //check mo is exists
                            result = this.QueryCustomInfo(_tmpMat.cust_id.ToString().Trim());
                            if (result.Status)
                            {
                                if (result.Datas.Length>0)
                                {//update
                                    var matId = result.Datas.ToList()[0].id;
                                    var matFlag = result.Datas.ToList()[0].is_valid;
                                    if (matFlag == "N")
                                    {
                                        continue;
                                    }
                                    _tmpMat.Update_User = "SYSTEM_JOB";
                                    //_tmpMat.Update_Time = DateTime.Now;
                                    _tmpMat.Id = matId;
                                    result = this.UpdateCustomInfo(_tmpMat);
                                }
                                else
                                {//insert
                                    _tmpMat.Create_User = "SYSTEM_JOB";
                                    //_tmpMat.Create_Time = DateTime.Now;
                                    result = this.InsertCustomInfo(_tmpMat);
                                }
                                //var matId = result.Datas.ToList()[0].id;
                                //var matFlag = result.Datas.ToList()[0].is_valid;
                                //if (matFlag == "N")
                                //{
                                //    continue;
                                //}
                                //_bExists = matId > 0 ? true : false;
                                //if (_bExists)
                                //{
                                //    //update
                                //    _tmpMat.Update_User = "SYSTEM_JOB";
                                //    //_tmpMat.Update_Time = DateTime.Now;
                                //    _tmpMat.Id = matId;
                                //    result = this.UpdateCustomInfo(_tmpMat);
                                //}
                                //else
                                //{
                                    
                                //}
                            }
                            if (!result.Status)
                            {
                                return result;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);

                result.Status = false;
                result.Message = ex.Message;
            }

            return result;
        }

        private ExecutionResult<dynamic> GetMESLastCustomERPDate()
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @"SELECT max(erp_create_time) erp_create_time,max(erp_update_time) erp_update_time 
                                FROM dbo.c_custom_info_t WHERE is_valid = 'Y'";
                    conn.Open();
                    result.Datas = conn.Query(sql).ToArray();

                    result.Message = "获取Custom数据更新时间成功";
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    result.Status = false;
                    result.Message = ex.Message;
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> QueryCustomInfo(string vCustId = null, string vIsValid = null)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @" SELECT TOP 1000 [id]
      ,[cust_id]      ,[cust_code]      ,[cust_name_cn]      ,[address_cn]      ,[address_en]      ,[tel_no]      ,[e_mail]      ,[contact]
      ,[principal]      ,[sales]      ,[fax_no]      ,[country]      ,[area]      ,[address2]      ,[remarks]      ,[erp_create_time]
      ,[erp_update_time]      ,[is_valid]      ,[create_user]      ,[create_time]      ,[update_user]      ,[update_time]  
                 FROM [scumes].[dbo].[c_custom_info_t] WHERE 1=1 ";
                    if (!String.IsNullOrEmpty(vCustId))
                    {
                        sql = sql + " AND [cust_id] = @mo";
                    }
                    if (!String.IsNullOrEmpty(vIsValid))
                    {
                        sql = sql + " AND [is_valid] = @isValid";
                    }

                    result.Datas = conn.Query(sql, new { mo = vCustId, isValid = vIsValid }).ToArray();
                    result.Status = true;
                    result.Message = "查询Cust信息成功.";
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "查询Cust基础信息异常。信息:" + ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> InsertCustomInfo(dynamic _tmpMat)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                //var _tmpMat = (CustomInfo)vModel;
                try
                {
                    string _insertSql = @"  
                             INSERT INTO  [dbo].[c_custom_info_t]([cust_id],[cust_code],[cust_name_cn],[address_cn],[address_en],[tel_no],[e_mail],[contact]
                            ,[principal],[sales],[fax_no],[country],[area],[address2],[remarks],[erp_create_time],[erp_update_time],[is_valid],[create_user]
                            ,[create_time])
                             VALUES(@cust_id,@cust_code,@cust_name_cn,@address_cn,@address_en,@tel_no,@e_mail,@contact,@principal,@sales,@fax_no,@country
                            ,@area,@address2,@remarks,@erp_create_time,@erp_update_time,@is_valid,@create_user,@create_time)    ";
                    var dbParams = new DynamicParameters();

                    //dbParams.Add("@id", _tmpMat.Id, DbType.Int32, ParameterDirection.Input);
                    dbParams.Add("@cust_id", _tmpMat.cust_id, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@cust_code", _tmpMat.cust_code, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@cust_name_cn", _tmpMat.cust_name_cn, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@address_cn", _tmpMat.address_cn, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@address_en", _tmpMat.address_en, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@tel_no", _tmpMat.tel_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@e_mail", _tmpMat.e_mail, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@principal", _tmpMat.principal, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@contact", _tmpMat.contact, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@sales", _tmpMat.sales, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@fax_no", _tmpMat.fax_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@country", _tmpMat.country, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@area", _tmpMat.area, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@address2", _tmpMat.address2, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@remarks", _tmpMat.remarks, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_create_time", _tmpMat.erp_create_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_update_time", _tmpMat.erp_update_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@is_valid", _tmpMat.is_valid, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@create_time", DateTime.Now, DbType.DateTime, ParameterDirection.Input);
                    dbParams.Add("@create_user", _tmpMat.Create_User, DbType.String, ParameterDirection.Input);

                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "添加Cust信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "添加Cust信息失败";
                    }
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "添加Cust信息异常:"+ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> UpdateCustomInfo(dynamic _tmpMat)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                //var _tmpMat = (CustomInfo)vModel;
                try
                {
                    string _insertSql = @"   UPDATE [dbo].[c_custom_info_t]
                                                 SET [cust_id]=@cust_id
                                                ,[cust_code]=@cust_code
                                                ,[cust_name_cn]=@cust_name_cn
                                                ,[address_cn]=@address_cn
                                                ,[address_en]=@address_en
                                                ,[tel_no]=@tel_no
                                                ,[e_mail]=@e_mail
                                                ,[contact]=@contact
                                                ,[principal]=@principal
                                                ,[sales]=@sales
                                                ,[fax_no]=@fax_no
                                                ,[country]=@country
                                                ,[area]=@area
                                                ,[address2]=@address2
                                                ,[remarks]=@remarks
                                                ,[erp_create_time]=@erp_create_time
                                                ,[erp_update_time]=@erp_update_time
                                                ,[is_valid]=@is_valid
                                                --,[create_user]=@create_user
                                                --,[create_time]=@create_time
                                                ,[update_user]=@update_user
                                                ,[update_time]=@update_time
                                                 WHERE [id]=@id   ";
                    var dbParams = new DynamicParameters();

                    dbParams.Add("@id", _tmpMat.Id, DbType.Int32, ParameterDirection.Input);
                    dbParams.Add("@cust_id", _tmpMat.cust_id, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@cust_code", _tmpMat.cust_code, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@cust_name_cn", _tmpMat.cust_name_cn, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@address_cn", _tmpMat.address_cn, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@address_en", _tmpMat.address_en, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@tel_no", _tmpMat.tel_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@e_mail", _tmpMat.e_mail, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@contact", _tmpMat.contact, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@principal", _tmpMat.principal, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@sales", _tmpMat.sales, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@fax_no", _tmpMat.fax_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@country", _tmpMat.country, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@area", _tmpMat.area, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@address2", _tmpMat.address2, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@remarks", _tmpMat.remarks, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_create_time", _tmpMat.erp_create_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_update_time", _tmpMat.erp_update_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@is_valid", _tmpMat.is_valid, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@update_time", DateTime.Now, DbType.DateTime, ParameterDirection.Input);
                    dbParams.Add("@update_user", _tmpMat.Update_User, DbType.String, ParameterDirection.Input);

                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "更新Cust信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "更新Cust信息失败";
                    }
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "更新Cust信息异常:"+ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> QueryERPNewCustomInfo(string vCreateTime, string vUpdateTime)
        {
            var result = new ExecutionResult<dynamic>();
            string sql = @"    SELECT TOP 300 MA001 AS cust_id,MA002 AS cust_code,MA003 AS cust_name_cn,MA023 AS address_cn,MA024 AS address_en,MA006 AS tel_no,
                  MA009 AS e_mail,MA005 AS contact,MA004 AS principal,MA016 AS sales, MA008 AS fax_no, MA019 AS country,MA018 AS area,
                  MA027 AS address2,MA049 AS remarks,CREATE_DATE AS ERP_Create_Time,MODI_DATE AS ERP_Update_Time
                  FROM dbo.COPMA  WHERE  (CREATE_DATE >@CREATE_DATE or MODI_DATE >@UPDATE_DATE)  ";
            var dbParams = new DynamicParameters();
            dbParams.Add("@CREATE_DATE", vCreateTime, DbType.String, ParameterDirection.Input);
            dbParams.Add("@UPDATE_DATE", vUpdateTime, DbType.String, ParameterDirection.Input);
            //链接ERP DB，查询物料信息
            using (IDbConnection conn = SqlCreator.Create(DataBaseType.SqlServer,this._erpConnString))
            {
                try
                {                    
                    result.Datas = conn.Query<dynamic>(sql, dbParams).ToArray();
                    result.Status = true;
                    result.Message = "Get ERP Custom success.";
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + result.Message);
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    result.Status = false;
                    result.Message = ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
    }

    public class CustomInfo
    {
        public int Id { get; set; }
        public string Cust_Id { get; set; }
        public string Cust_Code { get; set; }
        public string Cust_Name_Cn { get; set; }
        public string Address_Cn { get; set; }
        public string Address_En { get; set; }
        public string Tel_No { get; set; }
        public string E_Mail { get; set; }
        public string Contact { get; set; }
        public string Principal { get; set; }
        public string Sales { get; set; }
        public string Fax_No { get; set; }
        public string Country { get; set; }
        public string Area { get; set; }
        public string Address2 { get; set; }
        public string Remarks { get; set; }
        public string Erp_Create_Time { get; set; }
        public string Erp_Update_Time { get; set; }
        private string _isValid = "Y";
        public string Is_Valid
        {
            get { return this._isValid; }
            set { this._isValid = String.IsNullOrEmpty(value) ? this._isValid : value; }
        }
        private string _sCreateUser = "SYSTEM_JOB";
        public string Create_User
        {
            get { return this._sCreateUser; }
            set { this._sCreateUser = String.IsNullOrEmpty(value) ? this._sCreateUser : value; }
        }
        public DateTime Create_Time { get; set; }

        private string _sUpdateUser = "SYSTEM_JOB";
        public string Update_User
        {
            get { return this._sUpdateUser; }
            set { this._sUpdateUser = String.IsNullOrEmpty(value) ? this._sUpdateUser : value; }
        }
        public DateTime Update_Time { get; set; }
    }
}